package STUDIO.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import STUDIO.excepcion.DAOExcepcion;
import STUDIO.excepcion.DAOStudioExcepcion;
import STUDIO.modelo.Instrumentos;

import STUDIO.util.CONEXIONStudioBD;


public class InstrumentoDAO extends BaseStudioDAO {

	public Instrumentos insertar(Instrumentos vo) throws DAOStudioExcepcion {

		System.out.println("InstrumentoDAO: insertar()");
		String query = "INSERT INTO instrumentos (tipo,marca,modelo,observacion,basico_adicional) VALUES (?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			con = CONEXIONStudioBD.obtenerConexion();
			//con.setAutoCommit(false);
			stmt = con.prepareStatement(query);
			
			stmt.setString(1, vo.getTipo());
			stmt.setString(2, vo.getMarca());
			stmt.setString(3, vo.getModelo());
			stmt.setString(4, vo.getObservacion());
			stmt.setInt(5, vo.getBase_adi());
			
			System.out.println(query);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar Instrumentos");
			}

			int idi = 0;
			//se hace para generar el id
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				idi = rs.getInt(1);
			}
			vo.setId_instrumentos(idi);
			//
			//con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				throw new DAOStudioExcepcion(e.getMessage());
			}
			System.err.println(e.getMessage());
			throw new DAOStudioExcepcion(e.getMessage());
		} finally {
			try {
				con.setAutoCommit(true);
			} catch (SQLException e) {
				throw new DAOStudioExcepcion(e.getMessage());
			}
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;}
		
		public Collection<Instrumentos> listarinst() throws DAOExcepcion {
			Collection<Instrumentos> c = new ArrayList<Instrumentos>();
			Connection con = null;
			PreparedStatement stmt = null;
			ResultSet rs = null;
			try {
				con = CONEXIONStudioBD.obtenerConexion();
				String query = "select * from instrumentos";
				stmt = con.prepareStatement(query);
				rs = stmt.executeQuery();
				while (rs.next()) {
					Instrumentos vo = new Instrumentos();
					vo.setId_instrumentos(rs.getInt("idinstrumentos"));
					vo.setTipo(rs.getString("tipo"));
					vo.setMarca(rs.getString("marca"));
					vo.setModelo(rs.getString("modelo"));
					vo.setObservacion(rs.getString("observacion"));
					
					
					c.add(vo);
				}

			} catch (SQLException e) {
				System.err.println(e.getMessage());
				throw new DAOExcepcion(e.getMessage());
			} finally {
				this.cerrarResultSet(rs);
				this.cerrarStatement(stmt);
				this.cerrarConexion(con);
			}
			return c;
		
	}

}
